package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.User;
import com.edu.dao.BaseDao;
import com.edu.dao.UserDao;

public class UserDaoImpl implements UserDao {

	/**
	 * 如果是查询 
	 * 	1创建 Connection PreparedStatement ResultSet
	 * 	2创建要返回的对象 如果是单个默认为null 如果是多个 默认是一个没有数据的集合
	 *  3确定要执行的sql语句
	 *  4利用BaseDao 获取 连接
	 *  5预加载sql
	 *  6如果sql中有? ps填问好
	 *  7执行executeQuery 获取 结果集
	 *  8 if(res.next()) 只有一个结果  while(res.next())多个结果
	 *  9 解析结果集 把res里的数据 放入到 第2步的对象中
	 *  10 不要忘记释放资源
	 *  11 返回第二步的对象
	 *  
	 * 如果是增删改查
	 *	1创建 Connection PreparedStatement
	 * 	2创建int result，表示受影响的行数
	 *  3确定要执行的sql语句
	 *  4利用BaseDao 获取 连接
	 *  5预加载sql
	 *  6如果sql中有? ps填问好
	 *  7执行executeUpdate 获取行数 
	 *  8 不要忘记释放资源
	 *  9返回行数 
	 */
	@Override
	public List<User> selectAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<User> users = new ArrayList();
		String sql = "SELECT * FROM user";

		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()) {
				User user = new User();
				user.setUserId(res.getInt("userId"));
				user.setUsername(res.getString("username"));
				user.setPassword(res.getString("password"));
				user.setRealName(res.getString("realName"));
				user.setSex(res.getString("sex"));
				user.setBirthday(res.getDate("birthday"));
				users.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return users;
	}

	@Override
	public User selectUser(String username, String password) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		User user = null;
		String sql = "SELECT * FROM user WHERE username = ? and password = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			System.out.println(ps);
			res = ps.executeQuery();
			if(res.next()) {
				user = new User();
				user.setUserId(res.getInt("userId"));
				user.setUsername(res.getString("username"));
				user.setPassword(res.getString("password"));
				user.setRealName(res.getString("realName"));
				user.setSex(res.getString("sex"));
				user.setBirthday(res.getDate("birthday"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public User selectUserByUsername(String username) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		User user = null;
		String sql = "SELECT * FROM user WHERE username = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			System.out.println(ps);
			res = ps.executeQuery();
			if(res.next()) {
				user = new User();
				user.setUserId(res.getInt("userId"));
				user.setUsername(res.getString("username"));
				user.setPassword(res.getString("password"));
				user.setRealName(res.getString("realName"));
				user.setSex(res.getString("sex"));
				user.setBirthday(res.getDate("birthday"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public int insertUser(User user) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO user(username,password,realName,sex,birthday) "
				+ "VALUES (?,?,?,?,?)";
		
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getRealName());
			ps.setString(4, user.getSex());
			ps.setDate(5,new Date(user.getBirthday().getTime()));
			result = ps.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int updatePassword(String username, String password) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE user set password = ? WHERE username = ?";	
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, password);
			ps.setString(2, username);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

}
